﻿Public Class frmGastosComunes
    Dim vectorBlock(100, 2) As String
    Dim VectorDepartamento(16, 5) As String
    Dim vectorFormasDePago(10, 2) As String
    Dim vectorDeuda(,) As String = {}
    Private Sub frmGastosComunes_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CargaDatosIniciales()

    End Sub
    Private Sub CargaDatosIniciales()
        listadoBlock()
        listadoFormasDePago()
    End Sub

    Private Sub listadoBlock()
        Dim i As Integer
        conectar()
        
        cmd.Connection = con
        cmd.CommandText = "SELECT ID,Nombre FROM tBlock"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read
                vectorBlock(i, 0) = dr("ID").ToString
                vectorBlock(i, 1) = dr("Nombre").ToString
                cbBlock.Items.Add(vectorBlock(i, 1))
                i += 1
            End While

        End If
        dr.Close()
        
        desconectar()

    End Sub
    Private Sub listadoFormasDePago()
        Dim i As Integer
        conectar()

        cmd.Connection = con
        cmd.CommandText = "SELECT ID,Nombre FROM tFormasDePago"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read
                vectorFormasDePago(i, 0) = dr("ID").ToString
                vectorFormasDePago(i, 1) = dr("Nombre").ToString
                cbFormasDePago.Items.Add(vectorFormasDePago(i, 1))
                i += 1
            End While

        End If
        dr.Close()

        desconectar()

    End Sub
    Private Sub listadoDepartamento(ByVal IDBlock As Integer)
        Dim i As Integer
        Dim depa As String
        cbDepartamento.Items.Clear()
        conectar()

        cmd.Connection = con
        cmd.CommandText = "SELECT d.ID,d.IDBlock,d.NumeroActual,d.NumeroAnterior,p.Nombre,p.ApPaterno,p.ApMaterno "
        cmd.CommandText += "FROM tDepartamento d INNER JOIN tPersonas p ON p.ID=d.IDPropietario "
        cmd.CommandText += "WHERE p.IDTipoRelacion=1 AND d.IDBlock=" + IDBlock.ToString
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader
        If dr.HasRows Then
            While dr.Read()
                VectorDepartamento(i, 0) = dr("ID").ToString
                VectorDepartamento(i, 1) = dr("IDBlock").ToString
                VectorDepartamento(i, 2) = dr("NumeroActual").ToString
                VectorDepartamento(i, 3) = dr("NumeroAnterior").ToString
                VectorDepartamento(i, 4) = dr("Nombre").ToString + " " + dr("ApPaterno").ToString + " " + dr("ApMaterno").ToString
                depa = Trim(dr("NumeroActual").ToString) & " EX " & Trim(dr("NumeroAnterior").ToString)
                cbDepartamento.Items.Add(Trim(depa))
                i += 1
                depa = ""
            End While
        End If
        dr.Close()
        desconectar()
    End Sub


    Private Sub cbBlock_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbBlock.SelectedIndexChanged
        listadoDepartamento(CInt(cbBlock.Text))
    End Sub

 
    Private Sub cbDepartamento_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbDepartamento.SelectedIndexChanged
        Dim i, indiceGastosComunes, indiceCuotas, largo As Integer
        Dim departamento, depaInArray, strArray As String

        largo = VectorDepartamento.GetLength(0)

        For i = 0 To largo - 1
            departamento = Trim(cbDepartamento.Text.Substring(0, 2))
            depaInArray = VectorDepartamento(i, 2)
            If departamento = depaInArray Then
                txtPropietario.Text = VectorDepartamento(i, 4)
                vectorDeuda = rescataDeuda(VectorDepartamento(i, 0))
                Exit For
            End If
        Next
        i = 0
        If vectorDeuda.GetLength(0) > 0 Then
            'Limpia DataGridView
            dgvDeuda.Rows.Clear()
            'Limpia ComboBox selección entre fechas
            cbDesde.Text = ""
            cbDesde.Items.Clear()
            cbHasta.Text = ""
            cbHasta.Items.Clear()

            For i = 0 To vectorDeuda.GetLength(0) - 1
                If (vectorDeuda(i, 0) > 0) Then
                    If (vectorDeuda(i, 5) = 1) Then
                        dgvDeuda.Rows.Add()
                        strArray = vectorDeuda(i, 0)
                        dgvDeuda.Rows(indiceGastosComunes).Cells(1).Value = strArray
                        dgvDeuda.Rows(indiceGastosComunes).Cells(2).Value = vectorDeuda(i, 1)
                        cbDesde.Items.Add(vectorDeuda(indiceGastosComunes, 1))
                        cbHasta.Items.Add(vectorDeuda(indiceGastosComunes, 1))
                        dgvDeuda.Rows(indiceGastosComunes).Cells(3).Value = vectorDeuda(i, 2)
                        btnPagar.Enabled = True
                        indiceGastosComunes += 1
                    ElseIf (vectorDeuda(i, 5) = 2) Then
                        dgvDeudaArrastre.Rows.Add()
                        strArray = vectorDeuda(i, 0)
                        dgvDeudaArrastre.Rows(indiceCuotas).Cells(1).Value = strArray
                        dgvDeudaArrastre.Rows(indiceCuotas).Cells(2).Value = vectorDeuda(i, 1)
                        'cbDesde.Items.Add(vectorDeuda(i, 1))
                        'cbHasta.Items.Add(vectorDeuda(i, 1))
                        dgvDeudaArrastre.Rows(indiceCuotas).Cells(3).Value = vectorDeuda(i, 2)
                        dgvDeudaArrastre.Rows(indiceCuotas).Cells(4).Value = vectorDeuda(i, 6)
                        btnPagar.Enabled = True
                        indiceCuotas += 1
                    End If

                    
                Else
                    Exit For
                End If
            Next
        End If




              dgvDeuda.DefaultCellStyle.Font = New Font("Verdana", 10)
            dgvDeuda.AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
          



    End Sub
    Private Function rescataDeuda(ByVal ID As String) As String(,)
        Dim arregloDeuda(200, 6) As String
        Dim i As Integer

        '
        conectar()

        cmd.Connection = con

        ' Dim IdIn As OleDbParameter = testCMD.Parameters.Add &  ("@au_idIN", OleDbType.VarChar, 11) 
        'IdIn = cmd.Parameters.Add("IDDepa", Odbc.OdbcType.Int, 10)
        'IdIn.Direction = ParameterDirection.Input
        'IdIn.Value = ID
        cmd.CommandText = "CALL sp_deuda(" + ID.ToString + ")"
        cmd.CommandType = CommandType.StoredProcedure

        dr = cmd.ExecuteReader
        If dr.HasRows Then

            While dr.Read()
                arregloDeuda(i, 0) = dr(0).ToString
                arregloDeuda(i, 1) = dr("Periodo").ToString
                arregloDeuda(i, 2) = dr("Valor").ToString
                arregloDeuda(i, 3) = dr("Anho").ToString
                arregloDeuda(i, 4) = dr("Mes").ToString
                arregloDeuda(i, 5) = dr("Tipo").ToString
                arregloDeuda(i, 6) = dr("ID").ToString
                i += 1
            End While
        End If
        dr.Close()
        desconectar()
        Return arregloDeuda
    End Function


 
    Private Sub dgvDeuda_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDeuda.CellValueChanged, dgvDeudaArrastre.CellValueChanged
        Dim i, suma As Integer
        If sender.CurrentCell() Is Nothing Then

        Else
            If sender.CurrentCell().ColumnIndex = 0 Then
                For i = 0 To dgvDeuda.Rows.Count - 1
                    If dgvDeuda.Rows(i).Cells(0).Value = True Then
                        suma += dgvDeuda.Rows(i).Cells(3).Value
                    End If
                Next
                For i = 0 To dgvDeudaArrastre.Rows.Count - 1
                    If dgvDeudaArrastre.Rows(i).Cells(0).Value = True Then
                        suma += dgvDeudaArrastre.Rows(i).Cells(3).Value
                    End If
                Next
                txtTotal.Text = suma.ToString
            End If
        End If



    End Sub

    Private Sub btnPagar_Click(sender As Object, e As EventArgs) Handles btnPagar.Click
        Dim fa As Integer
        Dim idFormasDePago, lastInsertId As Integer
        Dim idDepa, idCuota As Integer
        Dim documento, fecha, periodo As String
        Dim ok As Boolean = True
        If MessageBox.Show("Si paga los periodos seleccionados ya no se podrán realizar modificaciones. Desea continuar?", "Advertencia", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            idFormasDePago = consultaIDFormasDePago(cbFormasDePago.Text)
            If idFormasDePago > 0 Then
                documento = txtNumeroDocumento.Text
                fecha = dtp1.Value.ToShortDateString
                conectar()
                cmd.Connection = con
                cmd.CommandText = "INSERT INTO tPagosRecibidos (Fecha,IDFormasDePago,NumeroDocumento,Total,PagadoPor,Comentarios,IdLogin) VALUES ('" + fecha + "'," + idFormasDePago.ToString + ",'" + documento.ToString + "'," + txtTotal.Text + ",'" + txtPagadoPor.Text + "','" + txtObservaciones.Text + "'," + usuarioID.ToString + ")"
                cmd.CommandType = CommandType.Text
                fa = cmd.ExecuteNonQuery
                desconectar()
                If fa > 0 Then
                    ' EXTRAER ID Ingresado
                    conectar()
                    cmd.Connection = con
                    cmd.CommandText = "SELECT MAX(ID) FROM tPagosRecibidos"
                    cmd.CommandType = CommandType.Text
                    dr = cmd.ExecuteReader
                    If dr.HasRows Then
                        dr.Read()
                        lastInsertId = dr(0)
                    End If
                    dr.Close()
                    desconectar()
                    'SI EL ID ES > 0 ENTONCES CONTINUAR 
                    If lastInsertId > 0 Then
                        If dgvDeuda.RowCount > 0 Then
                            For i = 0 To dgvDeuda.RowCount - 1
                                If (dgvDeuda.Rows(i).Cells(0).Value = True) Then
                                    conectar()
                                    cmd.Connection = con
                                    idDepa = dgvDeuda.Rows(i).Cells(1).Value
                                    periodo = dgvDeuda.Rows(i).Cells(2).Value
                                    cmd.CommandType = CommandType.Text
                                    cmd.CommandText = "UPDATE tGastosComunes SET IDPago=" + lastInsertId.ToString + " WHERE IDDepartamentos=" + idDepa.ToString + " AND Periodo='" + periodo + "'"
                                    fa = cmd.ExecuteNonQuery
                                    If fa <= 0 Then
                                        ok = False
                                        MessageBox.Show("Error al ingresar pago para periodo " + periodo)
                                    End If
                                    desconectar()
                                Else
                                    Exit For
                                End If
                            Next
                            If ok Then
                                MessageBox.Show("Se ha creado el pago para los periodos seleccionados.")
                                btnImprimir.Enabled = True
                                btnPagar.Enabled = False
                                txtNum.Text = lastInsertId.ToString
                            End If
                        End If
                        ' PAGO DE CUOTAS 
                        If dgvDeudaArrastre.RowCount > 0 Then
                            For i = 0 To dgvDeudaArrastre.RowCount - 1
                                If (dgvDeudaArrastre.Rows(i).Cells(0).Value = True) Then
                                    conectar()
                                    cmd.Connection = con
                                    idCuota = dgvDeudaArrastre.Rows(i).Cells(4).Value
                                    periodo = dgvDeudaArrastre.Rows(i).Cells(2).Value
                                    cmd.CommandType = CommandType.Text
                                    cmd.CommandText = "UPDATE tDeudaCuotas SET IDPagosRecibidos=" + lastInsertId.ToString + " WHERE ID=" + idCuota.ToString
                                    fa = cmd.ExecuteNonQuery
                                    If fa <= 0 Then
                                        ok = False
                                        MessageBox.Show("Error al ingresar pago para cuota " + periodo)
                                    End If
                                    desconectar()
                                Else
                                    Exit For
                                End If
                            Next
                            If ok Then
                                MessageBox.Show("Se ha creado el pago para la deuda seleccionada.")
                                btnImprimir.Enabled = True
                                btnPagar.Enabled = False
                                txtNum.Text = lastInsertId.ToString
                            Else
                                btnImprimir.Enabled = False
                                btnPagar.Enabled = False
                                txtNum.Text = lastInsertId.ToString
                            End If
                        End If

                    End If
                End If
            Else
                MessageBox.Show("Debe seleccionar la forma de pago.")
            End If

        End If



    End Sub
    Private Function consultaIDFormasDePago(ByVal texto As String)
        Dim largo, idFormaDePago As Integer
        Dim pagoInArray As String
        largo = vectorFormasDePago.GetLength(0)
        idFormaDePago = 0
        For i = 0 To largo - 1
            pagoInArray = vectorFormasDePago(i, 1)
            If texto = pagoInArray Then
                idFormaDePago = vectorFormasDePago(i, 0)



                Exit For
            End If
        Next
        Return idFormaDePago
    End Function

    Private Sub btnImprimir_Click(sender As Object, e As EventArgs) Handles btnImprimir.Click
        Dim frmVisor As New frmVisorDePago
        frmVisor.idPago = txtNum.Text
        frmVisor.Show()
    End Sub

    Private Sub btnCheck_Click(sender As Object, e As EventArgs) Handles btnCheck.Click
        Dim desde, hasta As String
        Dim fchDesde, fchHasta As Date
        Dim numDesde, numHasta As Integer

        desde = cbDesde.Text
        hasta = cbHasta.Text

        If (Trim(desde).Length = 7) And (Trim(hasta).Length = 7) Then
            fchDesde = CDate("01/" & desde)
            fchHasta = CDate("01/" & hasta)
            If fchHasta < fchDesde Then
                MessageBox.Show("La fecha de término debe ser superior a la fecha inicial.")
            Else
                numDesde = rescataIDdesdeDGV(desde)
                numHasta = rescataIDdesdeDGV(hasta)
                For i As Integer = numDesde To numHasta
                    dgvDeuda.Rows(i).Cells(0).Value = True
                Next
            End If

        End If



    End Sub
    Public Function rescataIDdesdeDGV(ByVal periodo As String) As Integer
        Dim id, i As Integer
        id = 0
        For i = 0 To dgvDeuda.Rows.Count - 1
            If dgvDeuda.Rows(i).Cells(2).Value = periodo Then
                id = i
                Exit For
            End If
        Next
        Return id
    End Function



  
End Class